const express = require('express');
const path = require('path');
const mysql = require('mysql');
const moment = require('moment');
const fs = require('fs');
const { getUnixTime } = require('./util/tool.js');
const app = express();
const artTemplate = require('art-template');
const express_template = require('express-art-template');
// const { ok } = require('assert');
app.use('/uploads', express.static(__dirname + '/uploads'));
app.use('/static', express.static(__dirname + '/static'));
let multer = require('multer');
let upload = multer({ dest: 'uploads/' })

app.use(express.json()) // for parsing application/json
app.use(express.urlencoded({ extended: true })) // for parsing application/x-www-form-urlencoded

//配置模板的路径
app.set('views', __dirname + '/view/');
//设置express_template模板后缀为.html的文件(不设这句话，模板文件的后缀默认是.art)
app.engine('html', express_template);
//设置视图引擎为上面的html
app.set('view engine', 'html');
// 过滤器
artTemplate.defaults.imports.dateFormat = function (time, format = 'YYYY-MM-DD HH:mm:ss') {
    return moment.unix(time).format(format);
}


// 连接数据库
let dbConfig = require('./config/dbconfig.js')
let connection = mysql.createConnection(dbConfig);

//连接mysql
connection.connect(function (err) {
    if (err) {
        throw err;
    }
    console.log('连接mysql成功');
})

let statusTextMap = {
    0: '<span class="green">已发布</span>',
    1: "<span class='red'>未发布</span>",
    2: "<span class='gray'>审核中</span>"
}
app.get('/', (req, res) => {
    // 编写sql
    let sql = `select t1.*,t2.cate_name from article  as t1 
    left join category t2 on t1.cat_id = t2.cate_id 
    where t1.is_delete = 0 order by t1.id desc`;

    // 执行sql
    connection.query(sql, (err, rows) => {
        // console.log('异步回调')
        // 查询数据分配模板引擎
        // console.log(rows);
        let data = rows.map((item) => {
            // let status_text = ''
            // if (item.status == 0) {
            //     status_text = "已发布"
            // } else if (item.status == 1) {
            //     status_text = "未发布"
            // } else {
            //     status_text = "审核中"
            // }
            item.status_text = statusTextMap[item.status]
            return item;
        })
        console.log(data)
        res.render('index.html', { articles: data })

    })

    // res.send('首页');
})

// 文章回收站列表
app.get('/recyclelist',(req,res)=>{
    // 1. 编写sql语句
    let sql = `select t1.*,t2.cate_name from article  as t1 
                left join category t2 on t1.cat_id = t2.cate_id 
                where t1.status = 1 and t1.is_delete = 1 order by t1.id desc`;
    // 2. 执行sql
    connection.query(sql,(err,rows)=>{
        console.log(rows); // [{title,status},{}]
        let data = rows.map((item)=>{
            item.status_text = statusTextMap[item.status]
            return item;
        })
        // 3. 把查询出来的数据分配到模板引擎中
        res.render('recycle.html',{articles:data})
    })
    
})

// 删除
app.get('/delete', (req, res) => {
    let { id } = req.query;
    let sql = `delete from article where id =${id}`;
    connection.query(sql, (err, result) => {
        if (result.affectedRows) {
            // ok
            res.redirect('/');
        } else {
            res.send("<script>alert('删除失败');location.href='/'</script>");
        }
    })
    // res.send('delete')
})

// 展示添加表单页面
app.get('/add', (req, res) => {
    let sql = "select * from category";
    connection.query(sql, (err, rows) => {
        res.render('add.html', { cats: rows })
    })
})
// 数据入库
app.post('/insert',upload.single('img'), (req, res) => {
    console.log('req.body:',req.body);
    console.log('req.file:',req.file);
    // 是否有图片
    let imgPath = '';
    if(req.file){
        let {originalname,filename,destination} = req.file
        let ext = originalname.substring( originalname.indexOf('.') ); // .png
        // 上传成功的文件进行重命名
        let oldPath = path.join( __dirname,destination,filename);
        let newPath = path.join( __dirname,destination,filename) + ext;
        // 数据库记录存放的路径
        imgPath = `${destination}${filename}${ext}`
        fs.renameSync(oldPath,newPath)
    }
    // 接收post
    let { title, author, cat_id, content, status } = req.body;
    let sql = 'insert into article(title,img,author,cat_id,content,status,add_time)values(?,?,?,?,?,?,?)';
    let nowTime = getUnixTime();
    let bind = [title,imgPath, author, cat_id, content, status, nowTime]
    connection.query(sql, bind, (err, result) => {
        if (result.affectedRows) {
            res.send("<script>alert('成功');location.href='/';</script>")
        } else {
            res.send("<script>alert('失败');location.href='/add';</script>")
        }
    })

})

app.get('/edit', (req, res) => {
    let { id } = req.query;
    let sql1 = `select * from article where id= ${id}`;
    connection.query(sql1, (err, rows1) => {
        let sql2 = "select * from category";
        connection.query(sql2, (err, rows2) => {
            res.render('edit.html', {
                article: rows1[0],
                cats: rows2
            })
        })
    })
})

app.post('/update', (req, res) => {
    let { id, title, author, status, cat_id, content } = req.body
    let update_time = getUnixTime();
    let bind = [title, author, status, cat_id, content, update_time, id]
    let sql = `update article set title = ?,author= ?,status= ?,cat_id= ?,content = ?,update_time = ? where id = ?`
    connection.query(sql, bind, (err, result) => {
        if (result.affectedRows) {
            res.redirect('/')
        } else {
            res.send('<script>alert("编辑失败");location.href="/";</script>')
        }
    })

})
// 实现文章加入回收站
app.get('/recycle',(req,res)=>{
    let { id = 0 } = req.query
    $sql =`update article set is_delete = 1 where id = ${id}`;
    connection.query($sql,(err,result)=>{
        let {affectedRows} = result;
        if(affectedRows){
            res.redirect('/')
        }else{
            res.send("<script>alert('加入失败');location.href='/';</script>")
        }
    })
})

// 实现文章还原
app.get('/restore',(req,res)=>{
    let { id = 0 } = req.query
    $sql =`update article set is_delete = 0 where id = ${id}`;
    connection.query($sql,(err,result)=>{
        let {affectedRows} = result;
        if(affectedRows){
            res.redirect('/')
        }else{
            res.send("<script>alert('还原失败');location.href='/';</script>")
        }
    })
})

//展示一个上传文件的表单
app.get('/addImg',(req,res)=>{
    res.render('addimg.html')
})

// 处理文件上传
app.post('/upload',upload.single('photo'),(req,res)=>{
    console.log(req.file); // 接收二进制
    console.log(req.body); // 文本数据
    let {originalname,filename,destination} = req.file;
    let ext = originalname.substring( originalname.indexOf('.') );
    // 上传成功文件进行重命名
    let oldPath = path.join( __dirname,destination,filename);
    let newPath = path.join( __dirname,destination,filename) + ext;
    fs.renameSync(oldPath,newPath)
    
    res.send('成功')
})

app.listen(8080, () => {
    console.log('服务器启动，端口号8080')
})